﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Web.Security;
using VASJ.Common;
using VASJ.Model.Entities;
using VASJ.Model.Helper;

namespace VASJ.Model
{
    public class DALHelper
    {
        public static void CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
        {
            try
            {
                return;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.ErrorFormat("{0}", ex.Message);
                throw;
            }
        }

        public static string CreateApplication(string appName, string address)
        {
            try
            {
                string appId = string.Empty;
                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(AspNetMembershipTable.AspNetApplication_CreateApplication, con) { CommandType = CommandType.StoredProcedure })
                    {
                        cmd.Parameters.Add(AspNetMembershipTable.ApplicationName, SqlDbType.NVarChar).Value = appName;

                        var outputParam = new SqlParameter(AspNetMembershipTable.ApplicationId, Guid.NewGuid());
                        outputParam.DbType = DbType.Guid;
                        outputParam.Direction = ParameterDirection.Output;

                        cmd.Parameters.Add(outputParam);

                        con.Open();

                        cmd.ExecuteNonQuery();

                        appId = (outputParam.Value.Equals(DBNull.Value) || outputParam.Value == null) ? string.Empty : outputParam.Value.ToString();

                    }
                }
                return appId;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.ErrorFormat("{0}", ex.Message);
                throw;
            }
        }

        public static List<MenuBarButtonItem> GetAllMenus()
        {
            try
            {
                List<MenuBarButtonItem> result = new List<MenuBarButtonItem>();

                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(MenuTable.GetAllMenus, con) { CommandType = CommandType.StoredProcedure })
                    {
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                int id = ReaderExt.GetInt(reader, MenuTable.IdColName);
                                string action = ReaderExt.SafeGetString(reader, MenuTable.ActionColName);
                                string caption = ReaderExt.SafeGetString(reader, MenuTable.CaptionColName);
                                string desc = ReaderExt.SafeGetString(reader, MenuTable.DescriptionColName);
                                string icon = ReaderExt.SafeGetString(reader, MenuTable.IconColName);
                                int parentId = ReaderExt.GetInt(reader, MenuTable.ParentIdColName);
                                short sortOrder = ReaderExt.GetSmallInt(reader, MenuTable.SortOrderColName);
                                bool state = ReaderExt.GetBoolean(reader, MenuTable.StateColName);
                                string tooltip = ReaderExt.SafeGetString(reader, MenuTable.TooltipColName);
                                result.Add(new MenuBarButtonItem() { Id = id, Caption = caption, Action = action, Description = desc, Icon = icon, ParentId = parentId, SortOrder = sortOrder, State = state, Tooltip = tooltip });
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.ErrorFormat("{0}", ex.Message);
                throw;
            }
        }

        public static List<Organization> GetAllOrganizations()
        {
            try
            {
                List<Organization> result = new List<Organization>();

                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(OrganizationTable.GetAllOrganizations, con) { CommandType = CommandType.StoredProcedure })
                    {
                        con.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string id = ReaderExt.SafeGetString(reader, OrganizationTable.IdColName);
                                string name = ReaderExt.SafeGetString(reader, OrganizationTable.NameColName);

                                result.Add(new Organization() { Id = id, Name = name });
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.ErrorFormat("{0}", ex.Message);
                throw;
            }
        }

        public static List<Organization> GetCompaniesByParent(string IdParent)
        {
            try
            {
                List<Organization> result = new List<Organization>();

                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(OrganizationTable.GetCompaniesByParent, con) { CommandType = CommandType.StoredProcedure })
                    {
                        cmd.Parameters.Add(OrganizationTable.ParentIdParameter, SqlDbType.VarChar, 10).Value = IdParent;

                        con.Open();

                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string id = ReaderExt.SafeGetString(reader, OrganizationTable.IdColName);
                                string name = ReaderExt.SafeGetString(reader, OrganizationTable.NameColName);
                                result.Add(new Organization() { Id = id, Name = name });
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.ErrorFormat("{0}", ex.Message);
                throw;
            }
        }

        public static List<LanguageItem> GetAllLocalizedData()
        {
            try
            {
                List<LanguageItem> result = new List<LanguageItem>();
                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(LocalizedDataTable.GetAllLocalizedData, con) { CommandType = CommandType.StoredProcedure })
                    {
                        con.Open();

                        SqlDataReader reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string id = ReaderExt.SafeGetString(reader, LocalizedDataTable.IdentifierColName);
                                string lan = ReaderExt.SafeGetString(reader, LocalizedDataTable.LanguageColName);
                                string title = ReaderExt.SafeGetString(reader, LocalizedDataTable.TitleColName);
                                string desc = ReaderExt.SafeGetString(reader, LocalizedDataTable.DescriptionColName);
                                result.Add(new LanguageItem() { Identifier = id, Language = lan, Title = title, Description = desc });
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.Error(ex.Message);
                throw;
            }
        }

        public static List<RoleInfor> GetAuthorize(string username, string orgId)
        {
            try
            {
                List<RoleInfor> result = new List<RoleInfor>();
                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings[Definition.KeToanConnectionString]))
                {
                    using (SqlCommand cmd = new SqlCommand(RoleInfoTable.GetAuthorize, con) { CommandType = CommandType.StoredProcedure })
                    {
                        con.Open();

                        SqlDataReader reader = cmd.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string userId = ReaderExt.SafeGetString(reader, RoleInfoTable.UserIdColName);
                                string roleId = ReaderExt.SafeGetString(reader, RoleInfoTable.RoleIdColName);
                                string organizationId = ReaderExt.SafeGetString(reader, RoleInfoTable.OrgIdColName);
                                result.Add(new RoleInfor() { UserId = userId, RoleId = roleId, OrgId = organizationId });
                            }
                        }
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                StackFrame stackFrame = new StackFrame();
                Logger.Log.Error(string.Format("{0}.{1}", stackFrame.GetMethod().DeclaringType.Name, stackFrame.GetMethod().Name));
                Logger.Log.Error(ex.Message);
                throw;
            }
        }
    }
}
